﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBAccess;
using System.Transactions;
using System.Data;
using SSPharma.Biz.Common;
using SSPharma.Common;
using System.Data.Common;
using SSPharma.Model;

namespace SSPharma.Biz.NhanSu
{
    class BizThemNhanVien
    {
        //khai báo hàm Common dùng để lấy các hàm dùng chung
        private BizCommon _BizCommon = new BizCommon();
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// lấy thông tin chính của nhân viên
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataNhanVienTheoMa(string Org, string Depart,string Ma)
        {
            SQLServer.Param[] Params = new SQLServer.Param[3];
            Params[0] = new SQLServer.Param("@ID_ORG", Org);
            Params[1] = new SQLServer.Param("@ID_Depart", Depart);
            Params[2] = new SQLServer.Param("@MaNhanVien", Ma);
            return Global.DataFactory.ReturnDataTable("sp_GetDataNhanVien", CommandType.StoredProcedure, Params);
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// lấy lịch sử chức danh
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataLSChucDanhTheoMa(string Org, string Depart, string Ma)
        {
            SQLServer.Param[] Params = new SQLServer.Param[3];
            Params[0] = new SQLServer.Param("@ID_ORG", Org);
            Params[1] = new SQLServer.Param("@ID_Depart", Depart);
            Params[2] = new SQLServer.Param("@MaNhanVien", Ma);
            return Global.DataFactory.ReturnDataTable("sp_GetDataLSChucDanh", CommandType.StoredProcedure, Params);
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// lấy lịch sử chức vụ
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataLSChucVuTheoMa(string Org, string Depart, string Ma)
        {
            SQLServer.Param[] Params = new SQLServer.Param[3];
            Params[0] = new SQLServer.Param("@ID_ORG", Org);
            Params[1] = new SQLServer.Param("@ID_Depart", Depart);
            Params[2] = new SQLServer.Param("@MaNhanVien", Ma);
            return Global.DataFactory.ReturnDataTable("sp_GetDataLSChucVu", CommandType.StoredProcedure, Params);
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// lấy lịch sử Hợp đồng
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataLSNganHangTheoMa(string Org, string Depart, string Ma)
        {
            SQLServer.Param[] Params = new SQLServer.Param[3];
            Params[0] = new SQLServer.Param("@ID_ORG", Org);
            Params[1] = new SQLServer.Param("@ID_Depart", Depart);
            Params[2] = new SQLServer.Param("@MaNhanVien", Ma);
            return Global.DataFactory.ReturnDataTable("sp_GetDataNganHangTheoNV", CommandType.StoredProcedure, Params);
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// lấy danh sách thân nhân
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataThanNhanTheoMa(string Org, string Depart, string Ma)
        {
            SQLServer.Param[] Params = new SQLServer.Param[3];
            Params[0] = new SQLServer.Param("@ID_ORG", Org);
            Params[1] = new SQLServer.Param("@ID_Depart", Depart);
            Params[2] = new SQLServer.Param("@MaNhanVien", Ma);
            return Global.DataFactory.ReturnDataTable("sp_GetDataThanNhan", CommandType.StoredProcedure, Params);
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// lấy danh sách trạng bị cá nhân
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataTrangBiCaNhanTheoMa(string Org, string Depart, string Ma)
        {
            SQLServer.Param[] Params = new SQLServer.Param[3];
            Params[0] = new SQLServer.Param("@ID_ORG", Org);
            Params[1] = new SQLServer.Param("@ID_Depart", Depart);
            Params[2] = new SQLServer.Param("@MaNhanVien", Ma);
            return Global.DataFactory.ReturnDataTable("sp_GetDataTrangBiCaNhan", CommandType.StoredProcedure, Params);
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Lay cau truc de luu du lieu xong database
        /// </summary>
        /// <returns></returns>
        public DataSet fs_LayCauTrucTable(string FormName,string Org, string Depart)
        {
            DataSet dsdata = new DataSet();
            SQLServer.Param[] Params = new SQLServer.Param[3];
            // lấy cấu trúc bảng nhân viên
            Params[0] = new SQLServer.Param("@ID_ORG", Org);
            Params[1] = new SQLServer.Param("@ID_Depart", Depart);
            Params[2] = new SQLServer.Param("@MaNhanVien", "");
            DataTable dtNhanVien = Global.DataFactory.ReturnDataTable("sp_GetDataNhanVien", CommandType.StoredProcedure, Params);
            dtNhanVien.TableName = "dt_NhanVien";
            dsdata.Tables.Add(dtNhanVien);
            // lấy cấu trúc bảng chức danh
            DataTable dtChucDanh = Global.DataFactory.ReturnDataTable("sp_GetDataLSChucDanh", CommandType.StoredProcedure, Params);
            dtChucDanh.TableName = "dt_ChucDanh";
            dsdata.Tables.Add(dtChucDanh);
            // lấy cấu trúc bảng chức vụ
            DataTable dtChucVu = Global.DataFactory.ReturnDataTable("sp_GetDataLSChucVu", CommandType.StoredProcedure, Params);
            dtChucVu.TableName = "dt_ChucVu";
            dsdata.Tables.Add(dtChucVu);
            // lấy cấu trúc bảng ngân hàng
            DataTable dtNganHang = Global.DataFactory.ReturnDataTable("sp_GetDataNganHangTheoNV", CommandType.StoredProcedure, Params);
            dtNganHang.TableName = "dt_NganHang";
            dsdata.Tables.Add(dtNganHang);
            // lấy cấu trúc bảng trang bị cá nhân
            DataTable dtTrangBi = Global.DataFactory.ReturnDataTable("sp_GetDataTrangBiCaNhan", CommandType.StoredProcedure, Params);
            dtTrangBi.TableName = "dt_TrangBi";
            dsdata.Tables.Add(dtTrangBi);
            // lấy cấu trúc bảng gia đình
            DataTable dtGiaDinh = Global.DataFactory.ReturnDataTable("sp_GetDataThanNhan", CommandType.StoredProcedure, Params);
            dtGiaDinh.TableName = "dt_GiaDinh";
            dsdata.Tables.Add(dtGiaDinh);
            Params = new SQLServer.Param[4];
            Params[0] = new SQLServer.Param("@FormName", FormName);
            Params[1] = new SQLServer.Param("@MaDonVi", Global.DonVi);
            Params[2] = new SQLServer.Param("@BoPhan", Global.BoPhan);
            Params[3] = new SQLServer.Param("@UserName", Global.UserName);
            DataTable dt_Default = Global.DataFactory.ReturnDataTable("sp_LayDuLieuMacDinh", CommandType.StoredProcedure, Params);
            dt_Default.TableName = "dt_Default";
            dsdata.Tables.Add(dt_Default);
            return dsdata;
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// kiem tra giua counter cu va counter moi
        /// </summary>
        /// <param name="p_sCounterOld"></param>
        /// <param name="p_sTienTo"></param>
        /// <returns></returns>
        public bool fb_KiemTraSoCounter(string p_sCounterOld, string p_sTienTo, string p_sDonVi, string p_sBoPhan, string p_sNam, string p_sThang)
        {
            string p_sCounterNew = _BizCommon.fs_GetCodeId(p_sTienTo, p_sDonVi, p_sBoPhan, p_sNam, p_sThang);
            if (p_sCounterNew.Equals(p_sCounterOld))
                return false;
            return true;
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Them danh muc tỷ giá.
        /// </summary>
        /// <param name="p_dtNhanVien"></param>
        /// <param name="TienTo"></param>
        public void fg_InsertNhanVien(DataSet p_dsNhanVien, string TienTo)
        {
            try
            {
                string query_NhanVien = "Select * from HR_NhanVien where 1 = 2";
                string query_ThanhNhan = "Select * from HR_ThanNhan where 1 = 2";
                string query_ChucDanh = "Select * from HR_LS_ChucDanh where 1 = 2";
                string query_ChucVu = "Select * from HR_LS_ChucVu where 1 = 2";
                string query_NganHang = "Select * from HR_NganHang where 1 = 2";
                string query_TrangBiCaNhan = "Select * from HR_NganHang where 1 = 2";
                string query_congto = "Select * from HT_CongToHienHanh_" + TienTo + " where 1 = 2";
                if (p_dsNhanVien.Tables["dt_NhanVien"].Rows[0].RowState == DataRowState.Added)
                {
                    DataTable p_dtCongTo = _BizCommon.ft_GetDataCongToHienHanh(TienTo, "", "", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString());
                    // TH01: lỗi
                    if (p_dtCongTo == null)
                    {
                        throw new System.Configuration.Provider.ProviderException("Không lấy được số chứng từ");
                    }
                    // TH01: tạo counter lưu xuống database
                    else if (p_dtCongTo.Rows.Count > 0)
                    {
                        using (TransactionScope scope = new TransactionScope())
                        {
                            string p_sCreateCounter = _BizCommon.fs_GetCodeId(TienTo, "", "", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString());
                            p_dsNhanVien.Tables["dt_NhanVien"].Rows[0]["MaNhanVien"] = p_sCreateCounter;
                            p_dtCongTo.Rows[0]["SoHienTai"] = !string.IsNullOrEmpty(p_dtCongTo.Rows[0]["SoHienTai"].ToString()) ? (Convert.ToInt64(p_dtCongTo.Rows[0]["SoHienTai"]) + 1) : 1;
                            Global.DataFactory.RunSaveData(p_dtCongTo, query_congto);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_NhanVien"], query_NhanVien);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_ChucDanh"], query_ChucDanh);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_ChucVu"], query_ChucVu);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_NganHang"], query_NganHang);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_TrangBi"], query_TrangBiCaNhan);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_GiaDinh"], query_ThanhNhan);
                            scope.Complete();
                            scope.Dispose();
                        }
                    }
                    // TH03: chưa có counter thì tạo counter đầu tiên
                    else if (p_dtCongTo.Rows.Count == 0)
                    {
                        using (TransactionScope scope = new TransactionScope())
                        {
                            // chạy hàm tạo counter đầu tiên.
                            string createCounter = _BizCommon.fs_CreateCodeId(TienTo, "", "", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString());
                            p_dsNhanVien.Tables["dt_NhanVien"].Rows[0]["MaNhanVien"] = createCounter;
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_NhanVien"], query_NhanVien);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_ChucDanh"], query_ChucDanh);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_ChucVu"], query_ChucVu);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_NganHang"], query_NganHang);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_TrangBi"], query_TrangBiCaNhan);
                            Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_GiaDinh"], query_ThanhNhan);
                            scope.Complete();
                            scope.Dispose();
                        }
                    }

                }
                else
                {
                    using (TransactionScope scope = new TransactionScope())
                    {
                        Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_NhanVien"], query_NhanVien);
                        Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_ChucDanh"], query_ChucDanh);
                        Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_ChucVu"], query_ChucVu);
                        Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_NganHang"], query_NganHang);
                        Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_TrangBi"], query_TrangBiCaNhan);
                        Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_GiaDinh"], query_ThanhNhan);
                        scope.Complete();
                        scope.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                StringBuilder strSQL = new StringBuilder();
                strSQL.Append("insert into HT_NhatKyLoi(ManHinh,SoCT,NgayPhatSinh,UserName,MaLoi,DienGiai,TenHam) values(@ManHinh,@SoCT,@NgayPhatSinh,@UserName,@MaLoi,@DienGiai,@TenHam)");
                SQLServer.Param[] Params = new SQLServer.Param[7];
                Params[0] = new SQLServer.Param("@ManHinh", "frmNhanVien");
                Params[1] = new SQLServer.Param("@SoCT", p_dsNhanVien.Tables["dt_NhanVienHeader"].Rows[0]["MaSoNhanVien"]);
                Params[2] = new SQLServer.Param("@NgayPhatSinh", LibraryFormatConverter.ConvertStringDDMMYYYYToYYYYMMDD(LibraryFormatConverter.SetStringDateDefault));
                Params[3] = new SQLServer.Param("@UserName", Global.UserName);
                Params[4] = new SQLServer.Param("@MaLoi", "");
                Params[5] = new SQLServer.Param("@DienGiai", ex.Message);
                Params[6] = new SQLServer.Param("@TenHam", "InsertNhanVien");
                Global.DataFactory.NonQuery(strSQL.ToString(), Params);
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Cap nhat danh muc tỷ giá
        /// </summary>
        /// <param name="dt"></param>
        public void fg_UpdateNhanVien(DataSet p_dsNhanVien)
        {
            try
            {
                string query_NhanVien = "Select * from HR_NhanVien where 1 = 2";
                string query_ThanhNhan = "Select * from HR_ThanNhan where 1 = 2";
                string query_ChucDanh = "Select * from HR_LS_ChucDanh where 1 = 2";
                string query_ChucVu = "Select * from HR_LS_ChucVu where 1 = 2";
                string query_NganHang = "Select * from HR_LS_NganHang where 1 = 2";
                string query_TrangBiCaNhan = "Select * from HR_NganHang where 1 = 2";
                using (TransactionScope scope = new TransactionScope())
                {
                    Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_NhanVien"], query_NhanVien);
                    Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_ChucDanh"], query_ChucDanh);
                    Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_ChucVu"], query_ChucVu);
                    Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_NganHang"], query_NganHang);
                    Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_TrangBi"], query_TrangBiCaNhan);
                    Global.DataFactory.RunSaveData(p_dsNhanVien.Tables["dt_GiaDinh"], query_ThanhNhan);
                    scope.Complete();
                    scope.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        /// <summary>
        /// User:TAMN 20/08/2014
        /// Lay counter tu them ma tien to
        /// </summary>
        /// <param name="MaTienTo"></param>
        /// <returns></returns>
        public string fs_LayCongToTuDong(string MaTienTo)
        {
            return _BizCommon.fs_GetCodeId(MaTienTo, "", "", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString());
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Lấy tên xã phường theo mã xã phường
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetTenXaPhuongTheoMa(string Ma)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HT_DM_PhuongXa", "TenPhuongXa", "MaPhuongXa = '" + Ma + "'");
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Lấy tên quận huyện theo mã
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetTenQuanHuyenTheoMa(string Ma)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HT_DM_QuanHuyen", "TenHuyen", "MaHuyen = '" + Ma + "'");
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Lấy tên quận huyện theo mã
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetTenTinhTPTheoMa(string Ma)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HT_DM_Tinh", "TenTinh", "MaTinh = '" + Ma + "'");
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Lấy chức danh theo mã
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetChucDanhTheoMa(string Ma)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HR_DM_ChucDanh", "Ten", "Ma = '" + Ma + "'");
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Lấy chức vụ theo mã
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetChucVuTheoMa(string Ma)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HR_DM_ChucVu", "Ten", "Ma = '" + Ma + "'");
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Lấy ngân hàng
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetNganHangTheoMa(string Ma)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HT_DM_NganHang", "TenNganHang", "MaNganHang = '" + Ma + "'");
        }
        /// <summary>
        /// User: TAMNN 20/08/2014
        /// Lấy thiết bị theo mã
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetThietBiTheoMa(string Ma)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HR_DM_ThietBi", "Ten", "Ma = '" + Ma + "'");
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: Lấy danh sách cho combobox giới tính
        /// </summary>
        /// <returns></returns>
        public IList<cboCommon> fl_DSDanhMuc_GioiTinh()
        {
            return _BizCommon.fl_DSDanhMucForcbo("MaGioiTinh", "TenGioiTinh", "HT_DM_GioiTinh");
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: Lấy danh sách cho combobox tỉnh
        /// </summary>
        /// <returns></returns>
        public IList<cboCommon> fl_DSDanhMuc_Tinh()
        {
            return _BizCommon.fl_DSDanhMucForcbo("MaTinh", "TenTinh", "HT_DM_Tinh");
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: Lấy danh sách cho combobox  dân tộc
        /// </summary>
        /// <returns></returns>
        public IList<cboCommon> fl_DSDanhMuc_DanToc()
        {
            return _BizCommon.fl_DSDanhMucForcbo("MaDanToc", "TenDanToc", "HT_DM_DanToc");
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: Lấy danh sách cho combobox tôn giáo
        /// </summary>
        /// <returns></returns>
        public IList<cboCommon> fl_DSDanhMuc_TonGiao()
        {
            return _BizCommon.fl_DSDanhMucForcbo("MaTonGiao", "TenTonGiao", "HT_DM_TonGiao");
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: Lấy danh sách cho combobox xã phường
        /// </summary>
        /// <returns></returns>
        public IList<cboCommon> fl_DSDanhMuc_XaPhuong()
        {
            return _BizCommon.fl_DSDanhMucForcbo("MaPhuongXa", "TenPhuongXa", "HT_DM_PhuongXa");
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: Lấy danh sách cho combobox quận huyện
        /// </summary>
        /// <returns></returns>
        public IList<cboCommon> fl_DSDanhMuc_QuanHuyen()
        {
            return _BizCommon.fl_DSDanhMucForcbo("MaHuyen", "TenHuyen", "HT_DM_QuanHuyen");
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: Lấy danh sách cho combobox quốc tịch
        /// </summary>
        /// <returns></returns>
        public IList<cboCheckBox> fl_DSDanhMuc_QuocTich()
        {
            List<cboCheckBox> items = new List<cboCheckBox>();
            cboCheckBox item;
            StringBuilder strSQL = new StringBuilder(300);
            strSQL.Append("SELECT MaQuocTich,TenQuocTich");
            strSQL.Append(" FROM HT_DM_QuocTich");
            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString()))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        item = new cboCheckBox();
                        if (dataReader.GetFieldType(0) == typeof(Int64))
                        {
                            item.Id = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetInt64(0).ToString();
                            item.Name =item.Id+  " " + (dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1));
                            item.IsSelected = false;
                        }
                        else
                        {
                            item.Id = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
                            item.Name = item.Id + " " + (dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1));
                            item.IsSelected = false;
                        }
                        items.Add(item);
                    }
                }
            }

            return items;
        }

    }
}
